package dao;

import java.sql.SQLException;
import java.util.ArrayList;

import pojo.ItemAttr;
import pojo.PollAttr;

public class PollDao extends DbDao{

    /**
     * 得到一行记录 
     */
    public PollAttr fetch() {
        try {
            while(this._resultSet.next()) {
                return this.genPollAttr();
            }
            return null;
        } catch(Exception ex) {
            return null;
        }
    }
   
    /**
     * 得到所有的查询结果集  
     */
    public ArrayList<PollAttr> fetchAll() {
        try {
            ArrayList<PollAttr> pollList  = new ArrayList<PollAttr>();
            while(this._resultSet.next()) {
                pollList.add(this.genPollAttr()); 
            }
            return pollList;
        } catch(Exception ex) {
            System.out.println("Error: " + ex.getMessage());
            return null;
        }
    }
    
    /**
     * 添加投票项目的重载
     * 
     * @param PollAttr pollAttr 投票项目属性对象
     * @return boolean true or false 是否添加成功
     */
    public boolean insert(PollAttr pollAttr) {
        try {
            String sql  = String.format("INSERT INTO `%s` " +
                                        "(`user_id`, `item_id`, `create_time`)" +
                                        " VALUES " +
                                        "('%d', '%d', '%s')",
                                        PollAttr.TableName,
                                        pollAttr.getUserId(),
                                        pollAttr.getItemId(),
                                        pollAttr.getCreateTime());
            return this._db.insert(sql);
        } catch(Exception ex) {
            return false;
        }
    }

    /**
     * 得到投票项目信息对象
     */
    private PollAttr genPollAttr() throws SQLException {
        PollAttr pollAttr  = new PollAttr();
        pollAttr.setUserId(this._resultSet.getInt("user_id"));
        pollAttr.setItemId(this._resultSet.getInt("item_id"));
        pollAttr.setCreateTime(this._resultSet.getTimestamp("create_time").toString());
        
        return pollAttr;
    }
   
    /**
     * 是否已经投过票
     */
    public boolean isPoll(int userId) {
        String sql  = String.format("SELECT * FROM `%s` WHERE `user_id` = %d;",
                                    PollAttr.TableName,
                                    userId);
        if(false == this.query(sql)) {
            return false;
        }
        if(null == this.fetch()) {
            return false;
        }
        
        return true;
    }
   
    /**
     * 删除提交项目 
     */
    public boolean delete(int userId, int itemId) {
        String sql  = String.format("DELETE FROM " +
                            		"`%s`" +
                            		" WHERE " +
                            		"`user_id` = %d" +
                            		" AND " +
                            		"`item_id` = %d",
                            		PollAttr.TableName,
                            		userId,
                            		itemId);
        
        return this.delete(sql);
    }
   
    /**
     * 这个方法还是有点纠结...要是放在这位置的话 
     */
    public ArrayList count() {
        String sql  = String.format("SELECT " +
        		"COUNT(`user_id`) as total_users," +
        		"`item_id`, " +
        		"`item_name`" +
        		" FROM " +
        		"`%s` as t1, `%s` as t2" +
        		" WHERE " +
        		"t1.item_id = t2.item_id" +
        		" GROUP BY" +
        		"`item_id`",
        		PollAttr.TableName,
        		ItemAttr.TableName);
        if(false == this.query(sql)) {
            return null;
        }
        try {
            ArrayList<PollAttr> pollList  = new ArrayList<PollAttr>();
            while(this._resultSet.next()) {
                pollList.add(this.genPollAttr()); 
            }
            return pollList;
        } catch(Exception ex) {
            System.out.println("Error: " + ex.getMessage());
            return null;
        }
    }
    
}
